import copy
from concurrent.futures import ThreadPoolExecutor
from time import time
from multiprocessing import Process, Queue

from utils.logging_tool.log_control import ERROR1, Listing_Error
from utils.mysqlUtils.db_utils import DB
from utils.noticeUtils.dingtalkControl import DingTalkSendMsg
from public_way import public_invoking
from utils.times_tool.time_control import now_time


class TestIsHotDataServer:
    """
    热同步冷服务
    - 获取不符合热数据条件的Listing数据同步至热数据打标表，再由另一服务同步至冷数据表
    - 人工刊登的Listing数据同步至热数据打标表，再由另一个服务同步至冷数据表
    """

    public_program = public_invoking()

    # 热同步冷服务 - 大宽表不符合热数据条件的冷数据 >>> 打标表 >>> 冷数据表
    def hot_change_cold_server_a(self, id, hot_sql):

        star_time = time()

        # 创建队列
        q = Queue()
        # 获取大宽表冷数据并推进队列
        erp_cold_data = self.public_program.get_cold_data(id, hot_sql)
        for n in erp_cold_data:
            # n["SalePrice"] = float(n["SalePrice"])
            q.put(n)

        # 根据渠道ID获取来源渠道对应的部门表ID
        ordersource_sql = f"select DepartmentId from aml_department_ordersource where OrderSourceId in ({id})"
        cold_id = self.public_program.cold_db(ordersource_sql)
        # 对部门表ID进行去重
        lis = []
        for i in cold_id:
            if i not in lis:
                lis.append(i)
        # 推导式将字典的key值存进列表
        ordersource_base = [n["DepartmentId"] for n in lis]
        cold_data = []
        # 遍历获取不同部门表的渠道数据
        for j in ordersource_base:
            sql = f"SELECT OrderSourceSKU, ASINorItemID, SKU FROM aml_amazon_product_base_dep{j}" \
                  f" WHERE OrderSourceID in ({id}) AND IsHotData = 0;"
            data = self.public_program.cold_db(sql)
            cold_data += data

        # 校验大宽表冷数据与冷数据表的冷数据
        pool = ThreadPoolExecutor(10)
        while not q.empty():
            try:
                except_json = q.get()
                pool.submit(self.public_program.threading_pool_comparison, except_json, cold_data)
            except:
                continue

        end_time = time()
        print(f"检测完成,耗时{round(end_time - star_time, 2)}")

        # # 测试报告
        # report = f"执行时间:{now_time()}    \n" \
        #          f"符合热转冷数据{len(erp_cold_data)}条   \n" \
        #          f"同步至冷数据表数据{len(sign_data)}条    \n" \
        #          f"发现异常数据{marking_error_data}条   \n" \
        #          f"异常数据明细:    \n" \
        #          f"{error_data}    \n" \
        # 钉钉预警
        # DingTalkSendMsg().send_migration_report(report)
        # return report

    # 热同步冷服务 - 人工刊登增量数据 >>> 打标表 >>> 冷数据表
    def hot_change_cold_server_b(self, id, time):

        id_list = id.split(',')
        for e in id_list:
            # 获取大宽表增量新刊登的Listing数据
            erp_sql = f"SELECT OrdersourceID, OrderSourceSKUID FROM RB_Amazon_Product_Base ( NOLOCK ) " \
                      f"WHERE CustomerID = 1 AND IsDelete = 0 AND OrderSourceID IN ({e}) AND OnShelfMode IN ( 1, 2, 3, 4, 5, 6, 7 ) " \
                      f"AND IsOffShelf <= 2 AND AddTime {time};"
            new_publish_listing_erp = self.public_program.erp_db(erp_sql)

            # 根据渠道ID获取来源渠道对应的部门表ID
            ordersource_sql = f"select DepartmentId from aml_department_ordersource where OrderSourceId in ({e})"
            baseid = self.public_program.cold_db(ordersource_sql)[0]["DepartmentId"]

            # 创建线程池
            pool = ThreadPoolExecutor(10)
            for data in new_publish_listing_erp:
                pool.submit(self.data_comparison, data, baseid)
            pool.shutdown()

        # # 测试报告
        # report = f"执行时间:{now_time()}    \n" \
        #          f"增量人工刊登数据{len(new_publish_listing)}条   \n" \
        #          f"同步至冷数据表数据{len(cold_data)}条    \n" \
        #          f"发现异常数据{err_cold}条   \n" \
        #          f"异常数据明细:    \n" \
        #          f"{err}    \n" \
        # 钉钉预警
        # DingTalkSendMsg().send_migration_report(report)
        # return report

    def data_comparison(self, data, baseid):

        sql = f"SELECT COUNT(*) FROM aml_amazon_product_base_dep{baseid} " \
              f"WHERE OrderSourceSKUID = {data['OrderSourceSKUID']} AND IsHotData = 1 AND OrderSourceID IN ({data['OrdersourceID']});"
        cold_base = DB("mysql_db_code_base")
        cold_res = cold_base.execute_sql_many(sql)
        if cold_res[0]["COUNT(*)"] == 0:
            Listing_Error.logger.error(f"{data}>>>>>>>>>存在异常")
        else:
            print(f"{data}>>>>>>>>>正常")


if __name__ == '__main__':

    # 人工刊登同步服务
    id = '6086,6087,6088,6442,6443,6450,6451,6507,6508,6559,6560,6561,6562,6563,6564,6591,6592,6593,6594,6595,6646,6656,6657,6658,6659,6660,6669,6670,6696,6697,6698,6699,6700,6701,6702'
    time = "BETWEEN '2023-3-1 00:00:00' AND '2023-3-7 23:59:59'"
    TestIsHotDataServer().hot_change_cold_server_b(id, time)


    # # 验证冷数据进打标表
    # id = '13469,6890,12917'
    # hot_sql = f"SELECT OrderSourceSKU, ASINorItemID, SKU FROM RB_Amazon_Product_Base ( NOLOCK ) WHERE CustomerID = 1 AND OrderSourceSKUID > 0 AND OrdersourceID IN ({id}) AND ID > 0 AND ISNULL( PublishTime, AddTime ) <= '2022-9-9 15:25:50' AND IsDelete = 0;"
    # TestIsHotDataServer().hot_change_cold_server_a(id, hot_sql)
